/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tces.controller;

import com.tces.model.Student;
import com.tces.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author Arnold
 */
public class StudentDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(StudentDAO.class);

    /**
     * this method will check the validity of the student
     *
     * @param studentIDNumber - School ID number of the student
     * @param studentPassword - Password of the student
     * @return Student object that based on the @password and @studentIDNumber
     * @throws Exception
     */
    public static Student authenticate(String studentIDNumber, String studentPassword) throws Exception {
        Student student = null;
        String sql = "SELECT\n"
                + "s.student_id,\n"
                + "pr.program_revision_name,\n"
                + "p.program_code,\n"
                + "CONCAT_WS(' - ',p.program_code,pr.program_revision_name) program_code_revision_name,\n"
                + "c.college_code,\n"
                + "c.college_id,\n"
                + "p.program_id,\n"
                + "pr.program_revision_id,\n"
                + "s.student_idno,\n"
                + "s.student_first_name,\n"
                + "s.student_middle_name,\n"
                + "s.student_last_name,\n"
                + "s.student_visible\n"
                + "FROM\n"
                + "students AS s\n"
                + "INNER JOIN program_revisions AS pr ON s.program_revision_id = pr.program_revision_id\n"
                + "INNER JOIN programs AS p ON pr.program_id = p.program_id\n"
                + "INNER JOIN colleges AS c ON p.college_id = c.college_id\n"
                + "WHERE s.student_password = MD5(?)\n"
                + "AND s.student_idno = ?";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setString(1, studentPassword);
        preparedStatement.setString(2, studentIDNumber);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();

        while (rs.next()) {
            student = new Student();
            student.setStudentID(rs.getInt("student_id"));
            student.setProgramID(rs.getInt("program_id"));
            student.setCollegeID(rs.getInt("college_id"));
            student.setProgramRevisionID(rs.getInt("program_revision_id"));
            student.setStudentVisible(rs.getInt("student_visible"));
            student.setCollegeCode(rs.getString("college_code"));
            student.setProgramRevisionName(rs.getString("program_revision_name"));
            student.setProgramCode(rs.getString("program_code"));
            student.setStudentIDNumber(rs.getString("student_idno"));
            student.setStudentFirstName(rs.getString("student_first_name"));
            student.setStudentMiddleName(rs.getString("student_middle_name"));
            student.setStudentLastName(rs.getString("student_last_name"));
            student.setProgramCodeRevisionName(rs.getString("program_code_revision_name"));
        }
        closeConnection(getConnectionObject());

        return student;
    }

    /**
     * this method will return all the students regardless if its softly deleted
     * or not
     *
     * @return
     * @throws Exception
     */
    public static List<Student> findAll() throws Exception {
        List<Student> studentList = new ArrayList<Student>();
        String sql = "SELECT\n"
                + "s.student_id,\n"
                + "pr.program_revision_name,\n"
                + "p.program_code,\n"
                + "CONCAT_WS(' - ',p.program_code,pr.program_revision_name) program_code_revision_name,\n"
                + "c.college_code,\n"
                + "c.college_id,\n"
                + "p.program_id,\n"
                + "pr.program_revision_id,\n"
                + "s.student_idno,\n"
                + "s.student_first_name,\n"
                + "s.student_middle_name,\n"
                + "s.student_last_name,\n"
                + "concat_ws(\n"
                + "		' ',\n"
                + "		concat_ws(\n"
                + "			', ',\n"
                + "			s.student_last_name,\n"
                + "			s.student_first_name\n"
                + "		),\n"
                + "		s.student_middle_name\n"
                + "	) AS student_full_name,\n"
                + "s.student_visible\n"
                + "FROM\n"
                + "students AS s\n"
                + "INNER JOIN program_revisions AS pr ON s.program_revision_id = pr.program_revision_id\n"
                + "INNER JOIN programs AS p ON pr.program_id = p.program_id\n"
                + "INNER JOIN colleges AS c ON p.college_id = c.college_id";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
//        preparedStatement.setInt(1, subjectOfferingID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        Student student;
        while (rs.next()) {
            student = new Student();
            student.setStudentID(rs.getInt("student_id"));
            student.setProgramID(rs.getInt("program_id"));
            student.setCollegeID(rs.getInt("college_id"));
            student.setProgramRevisionID(rs.getInt("program_revision_id"));
            student.setStudentVisible(rs.getInt("student_visible"));
            student.setCollegeCode(rs.getString("college_code"));
            student.setProgramRevisionName(rs.getString("program_revision_name"));
            student.setProgramCode(rs.getString("program_code"));
            student.setStudentIDNumber(rs.getString("student_idno"));
            student.setStudentFirstName(rs.getString("student_first_name"));
            student.setStudentMiddleName(rs.getString("student_middle_name"));
            student.setStudentLastName(rs.getString("student_last_name"));
            student.setStudentFullname(rs.getString("student_full_name"));
            student.setProgramCodeRevisionName(rs.getString("program_code_revision_name"));
            studentList.add(student);
        }
        closeConnection(getConnectionObject());

        return studentList;
    }

    public static List<Student> findAll(int collegeID) throws Exception {
        List<Student> studentList = new ArrayList<Student>();
        String sql = "SELECT\n"
                + "s.student_id,\n"
                + "pr.program_revision_name,\n"
                + "p.program_code,\n"
                + "CONCAT_WS(' - ',p.program_code,pr.program_revision_name) program_code_revision_name,\n"
                + "c.college_code,\n"
                + "c.college_id,\n"
                + "p.program_id,\n"
                + "pr.program_revision_id,\n"
                + "s.student_idno,\n"
                + "s.student_first_name,\n"
                + "s.student_middle_name,\n"
                + "s.student_last_name,\n"
                + "concat_ws(\n"
                + "		' ',\n"
                + "		concat_ws(\n"
                + "			', ',\n"
                + "			s.student_last_name,\n"
                + "			s.student_first_name\n"
                + "		),\n"
                + "		s.student_middle_name\n"
                + "	) AS student_full_name,\n"
                + "s.student_visible\n"
                + "FROM\n"
                + "students AS s\n"
                + "INNER JOIN program_revisions AS pr ON s.program_revision_id = pr.program_revision_id\n"
                + "INNER JOIN programs AS p ON pr.program_id = p.program_id\n"
                + "INNER JOIN colleges AS c ON p.college_id = c.college_id"
                + "WHERE c.college_id  = ?\n";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setInt(1, collegeID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        Student student;
        while (rs.next()) {
            student = new Student();
            student.setStudentID(rs.getInt("student_id"));
            student.setProgramID(rs.getInt("program_id"));
            student.setCollegeID(rs.getInt("college_id"));
            student.setProgramRevisionID(rs.getInt("program_revision_id"));
            student.setStudentVisible(rs.getInt("student_visible"));
            student.setCollegeCode(rs.getString("college_code"));
            student.setProgramRevisionName(rs.getString("program_revision_name"));
            student.setProgramCode(rs.getString("program_code"));
            student.setStudentIDNumber(rs.getString("student_idno"));
            student.setStudentFirstName(rs.getString("student_first_name"));
            student.setStudentMiddleName(rs.getString("student_middle_name"));
            student.setStudentLastName(rs.getString("student_last_name"));
            student.setStudentFullname(rs.getString("student_full_name"));
            student.setProgramCodeRevisionName(rs.getString("program_code_revision_name"));
            studentList.add(student);
        }
        closeConnection(getConnectionObject());

        return studentList;
    }

    public static int savePassword(String studentIDNumber, String studentPassword) throws Exception {
        int result = 0;
        try {
            String sql = "UPDATE students SET student_password = MD5(?) WHERE student_idno = ?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, studentPassword);
            preparedStatement.setString(2, studentIDNumber);
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
        }
        return result;
    }

    public static int add(Student student) throws Exception {
        try {
            String sql = "INSERT INTO students(student_idno,student_first_name,student_middle_name,student_last_name,program_revision_id, student_visible,student_password) values(?,?,?,?,?,?,?)";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, student.getStudentIDNumber());
            preparedStatement.setString(2, student.getStudentFirstName());
            preparedStatement.setString(3, student.getStudentMiddleName());
            preparedStatement.setString(4, student.getStudentLastName());
            preparedStatement.setInt(5, student.getProgramRevisionID());
            preparedStatement.setInt(6, student.getStudentVisible());
            preparedStatement.setString(7, student.getStudentIDNumber());
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int update(Student student) throws Exception {
        try {
            String sql = "UPDATE students SET student_idno=?,student_first_name=?,student_middle_name=?,student_last_name=?,program_revision_id=?, student_visible=?,student_password=md5(?) WHERE student_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, student.getStudentIDNumber());
            preparedStatement.setString(2, student.getStudentFirstName());
            preparedStatement.setString(3, student.getStudentMiddleName());
            preparedStatement.setString(4, student.getStudentLastName());
            preparedStatement.setInt(5, student.getProgramRevisionID());
            preparedStatement.setInt(6, student.getStudentVisible());
            preparedStatement.setString(7, student.getStudentIDNumber());
            preparedStatement.setInt(8, student.getStudentID());
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }
}
